<?php
namespace app\command;

use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\facade\Db;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\facade\Log;
use PhpOffice\PhpSpreadsheet\Reader\Exception;
use PhpOffice\PhpSpreadsheet\Shared\Date;

class ImportInventoryExcel extends Command
{
    protected function configure()
    {
        $this->setName('inventory:import')
            ->setDescription('导入库存Excel数据到数据库')
            ->addArgument('file', null, 'Excel文件路径', true);
    }

    protected function execute(Input $input, Output $output)
    {
        // 解除脚本限制
        set_time_limit(0);
        ini_set('memory_limit', '-1');

        $output->writeln("[INFO] 已解除脚本执行时间和内存限制");
        $output->writeln("<======================        开始处理文件          ===================>");
        Log::info("脚本启动，已解除时间和内存限制");
        Log::info("<======================        开始处理文件          ===================>");

        $file = $input->getArgument('file');

        // 标准化路径处理（解决Windows路径问题）
        $file = str_replace('\\', '/', $file);
        $file = realpath($file) ?: $file;

        $output->writeln("开始处理文件: {$file}");
        Log::info("开始处理文件: {$file}");

        // 检查文件是否存在和可读
        $this->checkFileAccessibility($file, $output);

        try {
            $startTime = microtime(true);
            $output->writeln("[DEBUG] 正在加载Excel文件...");
            Log::debug("正在加载Excel文件: {$file}");

            try {
                // 详细文件信息记录
                $fileSize = round(filesize($file) / 1048576, 2);
                $output->writeln("[DEBUG] 文件大小: {$fileSize}MB");
                Log::debug("文件大小: {$fileSize}MB");

                // 高效加载Excel文件（只读数据）
                $reader = IOFactory::createReaderForFile($file);
                $reader->setReadDataOnly(true);
                $spreadsheet = $reader->load($file);
            } catch (Exception $e) {
                throw new \Exception("Excel文件加载失败: " . $e->getMessage());
            } catch (\Throwable $e) {
                throw new \Exception("文件加载错误: " . $e->getMessage());
            }

            $memUsage = round(memory_get_usage() / 1048576, 2);
            $output->writeln("[DEBUG] Excel文件加载完成。内存用量: {$memUsage}MB");
            Log::debug("Excel文件加载完成，内存用量: {$memUsage}MB");

            // 获取工作表
            $sheetNames = $spreadsheet->getSheetNames();
            $output->writeln("[DEBUG] 工作表列表: " . implode(', ', $sheetNames));
            Log::debug("工作表列表: " . implode(', ', $sheetNames));

            $sheet = $spreadsheet->getSheetByName('库存');

            if (!$sheet) {
                $errMsg = "Excel文件中找不到[库存]工作表。存在的工作表: " . implode(', ', $sheetNames);
                $output->writeln("<error>{$errMsg}</error>");
                Log::error($errMsg);
                throw new \Exception($errMsg);
            }

            $output->writeln("[DEBUG] 已获取工作表: 库存");
            Log::debug("已成功获取[库存]工作表");

            // 获取表头和数据
            $header = $sheet->rangeToArray('A1:' . $sheet->getHighestColumn() . '1')[0];
            $rows = $sheet->rangeToArray('A2:' . $sheet->getHighestColumn() . $sheet->getHighestRow());

            $fieldMap = $this->getFieldIndices($header);
            $rowCount = count($rows);
            $output->writeln("找到 {$rowCount} 行数据...");
            Log::info("Excel读取完成，找到 {$rowCount} 行数据");

            // 输出表头映射详情（调试用）
            $this->logFieldMapping($header, $fieldMap, $output);

            // 分块处理数据
            $processed = 0;
            $errorCount = 0;
            $chunkSize = 100;
            $chunks = array_chunk($rows, $chunkSize);
            $chunkCount = count($chunks);

            $output->writeln("[DEBUG] 数据分块完成，共 {$chunkCount} 个块，每块 {$chunkSize} 行");
            Log::info("数据分块完成，共 {$chunkCount} 个块，每块 {$chunkSize} 行");

            $output->writeln("-----------------------------------------");
            $output->writeln("开始处理数据...");
            Log::info("开始处理数据...");

            foreach ($chunks as $chunkIndex => $chunkRows) {
                $chunkStart = $chunkIndex * $chunkSize + 2;
                $chunkEnd = $chunkStart + count($chunkRows) - 1;

                $output->writeln("=========================================");
                $output->writeln("处理数据块 #" . ($chunkIndex+1) . "/{$chunkCount} (行 {$chunkStart}-{$chunkEnd})");
                Log::info("处理数据块 #" . ($chunkIndex+1) . "/{$chunkCount} (行 {$chunkStart}-{$chunkEnd})");

                $memBefore = round(memory_get_usage() / 1048576, 2);
                $output->writeln("[MEMORY] 分块处理前内存: {$memBefore}MB");

                $chunkStartTime = microtime(true);

                foreach ($chunkRows as $rowIndex => $row) {
                    $line = $chunkStart + $rowIndex;

                    if ($processed > 0 && $processed % 10 === 0) {
                        $memUsage = round(memory_get_usage() / 1048576, 2);
                        $output->writeln("[PROGRESS] 已处理 {$processed} 行，内存: {$memUsage}MB");
                    }

                    if ($this->isEmptyRow($row)) {
                        $output->writeln("[SKIPPED] 跳过空行: {$line}");
                        Log::debug("跳过空行: {$line}");
                        continue;
                    }

                    try {
                        Db::startTrans();

                        $output->writeln("------------------------------");
                        $output->writeln("[DEBUG] 处理第 {$line} 行...");
                        Log::debug("处理第 {$line} 行...");

                        // ====================
                        // Step 1: 处理商品ID
                        // ====================
                        $output->writeln("[DATA] 商品编码: " . ($row[$fieldMap['商品编码']] ?? ''));
                        $productCode = $this->getValue($row, $fieldMap, '商品编码', $line,$output);
                        $productId = $this->getProductIdByCode($productCode, $line,$output);
                        $output->writeln("[INFO] 商品ID查询结果: {$productId}");
                        Log::debug("商品ID: {$productId}");

                        // ====================
                        // Step 2: 处理仓库ID
                        // ====================
                        $output->writeln("[DATA] 所属仓库: " . ($row[$fieldMap['所属仓库']] ?? ''));
                        $warehouseName = $this->getValue($row, $fieldMap, '所属仓库', $line,$output);
                        $warehouseId = $this->getWarehouseIdByName($warehouseName, $line);
                        $output->writeln("[INFO] 仓库ID查询结果: {$warehouseId}");
                        Log::debug("仓库ID: {$warehouseId}");

                        // ====================
                        // Step 3: 处理库存数据
                        // ====================
                        $inventoryData = $this->processInventory($row, $fieldMap, $line, $productId, $warehouseId);

                        // 检查库存是否已存在
                        $existingInventory = false;

                        if ($existingInventory) {
                            $inventoryId = $existingInventory['id'];
                            $output->writeln("[INFO] 第 {$line} 行: 库存已存在(ID:{$inventoryId})，跳过新增");
                            Log::info("行 {$line}: 库存已存在(ID:{$inventoryId})");
                        } else {
                            $inventoryId = Db::name('inventory')->insertGetId($inventoryData);
                            $output->writeln("[INFO] 第 {$line} 行: 新增库存(ID:{$inventoryId})");
                            Log::info("行 {$line}: 新增库存(ID:{$inventoryId})");
                        }

                        // ====================
                        // Step 4: 处理价格
                        // ====================
                        $usdPrice = $this->getValue($row, $fieldMap, '美元报价', $line,$output);
                        $cnyPrice = $this->getValue($row, $fieldMap, '人民币报价', $line,$output);

                        $output->writeln("[DATA] 美元报价: {$usdPrice}");
                        $output->writeln("[DATA] 人民币报价: {$cnyPrice}");

                        // 美元价格
                        $usdPriceId = $this->processPrice(
                            $row,
                            $fieldMap,
                            $inventoryId,
                            $warehouseId,
                            214, // 美元
                            '美元报价',
                            $line
                        );

                        // 人民币价格
                        $cnyPriceId = $this->processPrice(
                            $row,
                            $fieldMap,
                            $inventoryId,
                            $warehouseId,
                            4,   // 人民币
                            '人民币报价',
                            $line
                        );

                        $output->writeln("[INFO] 美元价格ID: " . ($usdPriceId ?? 'NULL'));
                        $output->writeln("[INFO] 人民币价格ID: " . ($cnyPriceId ?? 'NULL'));

                        // ====================
                        // Step 5: 处理折扣
                        // ====================
                        if ($usdPriceId) {
                            $discountCount = $this->processDiscount($row, $fieldMap, $usdPriceId, 'USD', $line);
                            $output->writeln("[INFO] 美元折扣处理: 新增/更新 {$discountCount} 条记录");
                        }

                        if ($cnyPriceId) {
                            $discountCount = $this->processDiscount($row, $fieldMap, $cnyPriceId, 'CNY', $line);
                            $output->writeln("[INFO] 人民币折扣处理: 新增/更新 {$discountCount} 条记录");
                        }

                        Db::commit();
                        $processed++;
                        Log::debug("第 {$line} 行处理成功");
                    } catch (\Exception $e) {
                        $errorCount++;
                        Db::rollback();
                        $errorMsg = "第 {$line} 行处理失败: " . $e->getMessage();
                        $output->writeln("<error>{$errorMsg}</error>");
                        Log::error("行 {$line} 错误: " . $e->getMessage());

                        // 记录堆栈追踪
                        $output->writeln("[DEBUG] 错误详情: " . $e->getTraceAsString());

                        // 连续错误达到5次时停止
                        if ($errorCount >= 5) {
                            $fatalError = "连续处理失败超过5次，终止脚本";
                            $output->writeln("<error>{$fatalError}</error>");
                            Log::error($fatalError);
                            throw new \Exception($fatalError);
                        }
                    }
                }

                // 释放内存
                unset($chunkRows);
                gc_collect_cycles();

                $chunkTime = round(microtime(true) - $chunkStartTime, 2);
                $memAfter = round(memory_get_usage()/1048576, 2);
                $output->writeln("[MEMORY] 分块 #".($chunkIndex+1)."处理完成，内存: {$memAfter}MB，耗时: {$chunkTime}秒");
                Log::info("分块 #".($chunkIndex+1)."处理完成，内存: {$memAfter}MB，耗时: {$chunkTime}秒");
            }

            $processTime = round(microtime(true) - $startTime, 2);
            $output->writeln("=========================================");
            $output->writeln("<info>处理完成! 耗时 {$processTime} 秒，成功 {$processed} 行，失败 {$errorCount} 行</info>");
            Log::info("库存导入完成，耗时: {$processTime}秒，成功: {$processed}行，失败: {$errorCount}行");

            // 最终内存报告
            $finalMem = round(memory_get_peak_usage() / 1048576, 2);
            $output->writeln("[MEMORY] 内存峰值使用: {$finalMem}MB");
            Log::info("内存峰值使用: {$finalMem}MB");
            $output->writeln("<======================        文件处理结束          ===================>");
            Log::info("<======================        文件处理结束          ===================>");

        } catch (\Exception $e) {
            $error = "处理失败: " . $e->getMessage();
            $output->writeln("<error>{$error}</error>");
            Log::error("Excel导入失败: {$error}");

            // 错误时记录内存状态
            $errorMem = round(memory_get_usage() / 1048576, 2);
            $output->writeln("[MEMORY] 终止时内存: {$errorMem}MB");
        }
    }

    /**
     * 文件可访问性检查
     */
    protected function checkFileAccessibility(string $file, Output $output)
    {
        if (!file_exists($file)) {
            $errMsg = "文件不存在: {$file}";
            $output->writeln("<error>{$errMsg}</error>");
            Log::error($errMsg);
            throw new \Exception($errMsg);
        }

        if (!is_readable($file)) {
            $errMsg = "文件不可读: {$file}";
            $output->writeln("<error>{$errMsg}</error>");
            Log::error($errMsg);
            throw new \Exception($errMsg);
        }

        $ext = pathinfo($file, PATHINFO_EXTENSION);
        if (!in_array(strtolower($ext), ['xlsx', 'xls'])) {
            $errMsg = "文件类型错误: {$ext}，仅支持.xlsx或.xls";
            $output->writeln("<error>{$errMsg}</error>");
            Log::error($errMsg);
            throw new \Exception($errMsg);
        }

        $fileSize = round(filesize($file) / 1048576, 2);
        $output->writeln("[DEBUG] 文件验证通过: 大小={$fileSize}MB");
        Log::debug("文件验证通过: 大小={$fileSize}MB");
    }

    /**
     * 记录字段映射详情
     */
    protected function logFieldMapping(array $headers, array $fieldMap, Output $output)
    {
        $output->writeln("[DEBUG] 字段映射详情:");
        $logContent = "字段映射详情:\n";

        foreach ($fieldMap as $field => $index) {
            $line = sprintf("  %-20s => %-5s (Excel列: %s)",
                $field,
                $index,
                $this->numberToExcelColumn($index+1)
            );
            $output->writeln($line);
            $logContent .= $line . "\n";
        }

        Log::info($logContent);
    }

    /**
     * 数字转换为Excel列名 (A, B, C, ...)
     */
    protected function numberToExcelColumn($number) {
        $column = '';
        while ($number > 0) {
            $mod = ($number - 1) % 26;
            $column = chr(65 + $mod) . $column;
            $number = (int)(($number - $mod) / 26);
        }
        return $column;
    }

    /**
     * 安全获取单元格值（修复null问题）
     */
    protected function getValue(array $row, array $fieldMap, string $field, int $line, Output $output): ?string
    {
        if (!isset($fieldMap[$field])) {
            $errMsg = "缺少必要字段: {$field} (行:{$line})";
            $output->writeln("<error>{$errMsg}</error>");
            Log::error($errMsg);
            throw new \Exception($errMsg);
        }

        $value = $row[$fieldMap[$field]] ?? '';
        return trim((string)$value);
    }

    /**
     * 通过商品编码获取商品ID
     */
    protected function getProductIdByCode(string $code, int $line, Output $output): int
    {
        if (empty($code)) {
            $errMsg = "第 {$line} 行: 商品编码不能为空";
            $output->writeln("<error>{$errMsg}</error>");
            Log::error($errMsg);
            throw new \Exception($errMsg);
        }

        $product = Db::name('product')
            ->where('code', $code)
            ->where('is_del', 0)
            ->find();

        if (!$product) {
            $errMsg = "第 {$line} 行: 未找到商品编码: {$code}";
            $output->writeln("<error>{$errMsg}</error>");
            Log::error($errMsg);
            throw new \Exception($errMsg);
        }

        return $product['id'];
    }

    /**
     * 通过仓库名称获取仓库ID
     */
    protected function getWarehouseIdByName(string $name, int $line ): int
    {
        $output = $this->output;
        if (empty($name)) {
            $errMsg = "第 {$line} 行: 仓库名称不能为空";
            $output->writeln("<error>{$errMsg}</error>");
            Log::error($errMsg);
            throw new \Exception($errMsg);
        }

        $warehouse = Db::name('warehouse')
            ->where('name', $name)
            ->where('is_del', 0)
            ->find();

        if (!$warehouse) {
            $errMsg = "第 {$line} 行: 未找到仓库名称: {$name}";
            $output->writeln("<error>{$errMsg}</error>");
            Log::error($errMsg);
            throw new \Exception($errMsg);
        }

        return $warehouse['id'];
    }

    /**
     * 获取ccname_ids（可售国家分类ID） - 优化处理一级仓库ID逻辑
     */
    protected function getCcnameIds(int $warehouseId): ?string
    {
        $warehouse = Db::name('warehouse')
            ->where('id', $warehouseId)
            ->find();

        // 优化点1：如果fl_warehouse_id为空或为0，使用当前仓库ID作为一级仓库ID
        $flWarehouseId = $warehouse['fl_warehouse_id'] ?? null;
        if (empty($flWarehouseId) || $flWarehouseId == 0) {
            $flWarehouseId = $warehouseId;
            Log::debug("仓库ID:{$warehouseId} 无一级仓库ID，使用自身作为一级仓库ID");
        }

        $ccNames = Db::name('cc_names')
            ->whereRaw("FIND_IN_SET(?, sellable_wids)", [$flWarehouseId])
            ->where('is_del', 0)
            ->column('id');

        return $ccNames ? implode(',', $ccNames) : null;
    }

    /**
     * 获取字段索引映射（修复null问题）
     */
    protected function getFieldIndices(array $headers): array
    {
        $indices = [];
        foreach ($headers as $index => $header) {
            // 确保header不是null，如果是null则转换为空字符串
            $trimmedHeader = trim($header ?? '');
            $indices[$trimmedHeader] = $index;
        }
        return $indices;
    }

    /**
     * 检查是否空行
     */
    protected function isEmptyRow(array $row): bool
    {
        foreach ($row as $value) {
            if (!empty($value) && $value !== null && trim($value) !== '') {
                return false;
            }
        }
        return true;
    }

    /**
     * 处理库存数据 - 优化提篮子库存处理
     */
    protected function processInventory(
        array $row,
        array $fieldMap,
        int $line,
        int $productId,
        int $warehouseId
    ): array
    {
        $output = $this->output;

        // 获取所有字段值（详细记录）
        $inventoryType = $this->getValue($row, $fieldMap, '库存类型', $line,$output);
        $ifTlz = $this->getValue($row, $fieldMap, '是否提篮子', $line,$output);
        $allowZeroSale = $this->getValue($row, $fieldMap, '是否允许零库存销售', $line,$output);
        $isSale = $this->getValue($row, $fieldMap, '是否可售', $line,$output);
        $arrivalDate = $this->getValue($row, $fieldMap, '期货到货时间', $line,$output);
        $qty = $this->getValue($row, $fieldMap, '可用库存数量', $line,$output);

        $output->writeln("[DATA] 库存类型: {$inventoryType}");
        $output->writeln("[DATA] 是否提篮子: {$ifTlz}");
        $output->writeln("[DATA] 是否允许零库存销售: {$allowZeroSale}");
        $output->writeln("[DATA] 是否可售: {$isSale}");
        $output->writeln("[DATA] 期货到货时间: {$arrivalDate}");
        $output->writeln("[DATA] 可用库存数量: {$qty}");

        $data = [
            'productid' => $productId,
            'wid' => $warehouseId,
            'create_time' => date('Y-m-d H:i:s'),
            'source_table' => 'excel_import',
            'source_id' => $line,
            'is_del' => 0,
            'initial_qty' => (int)$qty ?: 0
        ];

        // 处理必填字段
        $data['is_futures'] = $this->convertInventoryType($inventoryType);
        $data['if_tlz'] = $this->convertBool($ifTlz);
        $data['allow_zero_stock_sale'] = $this->convertBool($allowZeroSale);
        $data['is_sale'] = $this->convertBool($isSale);
        $data['arrival_date'] = $this->formatDate($arrivalDate);

        $output->writeln("[INFO] 转换后库存类型: {$data['is_futures']}");
        $output->writeln("[INFO] 是否提篮子: {$data['if_tlz']}");
        $output->writeln("[INFO] 允许零库存销售: {$data['allow_zero_stock_sale']}");
        $output->writeln("[INFO] 是否可售: {$data['is_sale']}");
        $output->writeln("[INFO] 到货日期: {$data['arrival_date']}");

        // 提篮子库存特殊处理 - 优化点2：强制设置可售和允许零库存销售
        if ($data['if_tlz']) {
            // 强制设置为1（是），即使Excel中为空
            $data['allow_zero_stock_sale'] = 1;
            $data['is_sale'] = 1;

            $output->writeln("[INFO] 提篮子库存，设置ccname_ids: " . ($ccIds ?? 'NULL'));
            $output->writeln("[INFO] 提篮子库存，强制设置允许零库存销售=1，是否可售=1");
        }
        $ccIds = $this->getCcnameIds($warehouseId);
        $data['ccname_ids'] = $ccIds;
        // 库存数量
        $data['available_qty'] = $data['initial_qty'];
        $data['convertible_qty'] = $data['initial_qty'];
        $data['lock_qty'] = 0;

        return $data;
    }

    /**
     * 处理商品价格
     */
    protected function processPrice(
        array $row,
        array $fieldMap,
        int $inventoryId,
        int $warehouseId,
        int $currencyId,
        string $priceField,
        int $line
    ): ?int
    {
        $output = $this->output;
        $priceValue = $this->getValue($row, $fieldMap, $priceField, $line,$output);

        if (empty($priceValue) || !is_numeric($priceValue) || $priceValue <= 0) {
            $output->writeln("[INFO] {$priceField} 无效，跳过处理");
            Log::info("行 {$line}: {$priceField} 无效: {$priceValue}");
            return null;
        }

        $price = (float)$priceValue;
        $output->writeln("[INFO] 处理价格: {$priceField} = {$price}");
        Log::debug("行 {$line}: 处理价格 {$priceField} = {$price}");

        $priceData = [
            'inventory_id' => $inventoryId,
            'wid' => $warehouseId,
            'transaction_currency_id' => $currencyId,
            'price' => $price,
            'update_time' => date('Y-m-d H:i:s'),
            'is_del' => 0
        ];

        // 检查价格是否已存在
        $existingPrice = Db::name('product_price')
            ->where([
                'inventory_id' => $inventoryId,
                'wid' => $warehouseId,
                'transaction_currency_id' => $currencyId
            ])
            ->find();

        if ($existingPrice) {
            Db::name('product_price')
                ->where('id', $existingPrice['id'])
                ->update($priceData);
            $output->writeln("[INFO] 更新现有价格 (ID:{$existingPrice['id']})");
            Log::info("行 {$line}: 更新价格 ID:{$existingPrice['id']}");
            return $existingPrice['id'];
        } else {
            $priceData['create_time'] = date('Y-m-d H:i:s');
            $priceId = Db::name('product_price')->insertGetId($priceData);
            $output->writeln("[INFO] 新增价格 (ID:{$priceId})");
            Log::info("行 {$line}: 新增价格 ID:{$priceId}");
            return $priceId;
        }
    }

    /**
     * 处理折扣数据
     */
    protected function processDiscount(
        array $row,
        array $fieldMap,
        int $priceId,
        string $currencyPrefix,
        int $line
    ): int
    {
        $output = $this->output;
        $processedDiscounts = 0;
        $output->writeln("[INFO] 开始处理折扣 (价格ID:{$priceId})");

        $baseField = ($currencyPrefix === 'USD') ? '美元报价' : '人民币报价';
        $startIndex = $fieldMap[$baseField] + 1;

        for ($level = 1; $level <= 5; $level++) {
            $qtyIndex = $startIndex + ($level - 1) * 2;
            $priceIndex = $qtyIndex + 1;

            if ($priceIndex >= count($row)) {
                $output->writeln("[INFO] 折扣级别 {$level} 超出范围，停止处理");
                break;
            }

            $qty = $row[$qtyIndex] ?? null;
            $price = $row[$priceIndex] ?? null;

            $output->writeln("[DEBUG] 折扣级别 {$level}: 数量={$qty}, 价格={$price}");

            if (!$this->isValidDiscount($qty, $price, $level)) {
                $output->writeln("[INFO] 折扣级别 {$level} 无效，跳过");
                continue;
            }

            $discount = [
                'level' => $level,
                'num' => (int)$qty,
                'price' => (float)$price,
                'price_id' => $priceId,
                'update_time' => date('Y-m-d H:i:s')
            ];

            // 检查折扣是否已存在
            $existingDiscount = Db::name('product_discount')
                ->where([
                    'price_id' => $priceId,
                    'level' => $level
                ])
                ->find();

            if ($existingDiscount) {
                Db::name('product_discount')
                    ->where('id', $existingDiscount['id'])
                    ->update($discount);
                $output->writeln("[INFO] 更新折扣 (ID:{$existingDiscount['id']})");
                Log::info("行 {$line}: 更新折扣 ID:{$existingDiscount['id']}");
            } else {
                $discount['create_time'] = date('Y-m-d H:i:s');
                $discountId = Db::name('product_discount')->insertGetId($discount);
                $output->writeln("[INFO] 新增折扣 (ID:{$discountId})");
                Log::info("行 {$line}: 新增折扣 ID:{$discountId}");
            }

            $processedDiscounts++;
        }

        return $processedDiscounts;
    }

    /**
     * 检查折扣是否有效
     */
    protected function isValidDiscount($qty, $price, $level): bool
    {
        if ($level == 1 && $qty == 0) return false;
        return !empty($qty) && is_numeric($qty) &&
            !empty($price) && is_numeric($price) &&
            $qty > 0 && $price > 0;
    }

    /**
     * 转换库存类型（增强空值和错误处理）
     */
    protected function convertInventoryType($value): int
    {
        if (empty($value)) {
            Log::warning("库存类型为空，使用默认值(现货)");
            return 0;
        }

        $value = trim($value);

        switch ($value) {
            case '现货': return 0;
            case '在途期货': return 1;
            case '大期货': return 2;
            default:
                Log::warning("无法识别的库存类型: {$value}, 使用默认值(现货)");
                return 0;
        }
    }

    /**
     * 转换布尔值字段（修复空值问题）
     */
    protected function convertBool($value): int
    {
        if ($value === null || $value === '') {
            Log::debug("布尔值为空，使用默认值(0)");
            return 0;
        }

        if (is_numeric($value)) {
            return $value > 0 ? 1 : 0;
        }

        $normalized = strtolower(trim($value));

        $trueValues = ['是', 'yes', 'true', '1', 'y', '对'];
        $falseValues = ['否', 'no', 'false', '0', 'n', '错'];

        if (in_array($normalized, $trueValues)) {
            return 1;
        }

        if (in_array($normalized, $falseValues)) {
            return 0;
        }

        Log::debug("无法识别的布尔值: {$value}, 使用默认值(0)");
        return 0;
    }

    /**
     * 格式化日期（增强兼容性）
     */
    protected function formatDate($value): ?string
    {
        if (empty($value)) {
            Log::debug("日期为空，返回NULL");
            return null;
        }

        try {
            if (is_numeric($value)) {
                return Date::excelToDateTimeObject($value)->format('Y-m-d');
            }

            $timestamp = strtotime($value);
            if ($timestamp === false) {
                Log::warning("无法解析日期: {$value}");
                return null;
            }

            return date('Y-m-d', $timestamp);
        } catch (\Exception $e) {
            Log::error("日期格式化错误: {$value} - " . $e->getMessage());
            return null;
        }
    }
}